package bddaccess;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import modex.Paragraphe;
import modex.Terme;
import modex.TermePara;

public class TermeParaDAO extends DAO<TermePara> {

	@Override
	public TermePara create(TermePara obj) {
		try {
			PreparedStatement prepare = 
				this.connect
                	.prepareStatement(
                			"INSERT INTO TermePara " +
                			"(idTerme, idPara, poids, position, score) " +
                			"VALUES(?, ?, ?, ?, ?)"        			
                	);
			
			prepare.setInt(1, obj.getIdTerme());
			prepare.setInt(2, obj.getIdPara());
			prepare.setInt(3, obj.getPoids());
			prepare.setString(4, obj.getPosition());
			prepare.setFloat(5, obj.getScore());
			
			prepare.executeUpdate();

			obj = this.find(obj.getIdTerme(), obj.getIdPara());

	    } catch (SQLException e) {
	            e.printStackTrace();
	    }
	    return obj;
	}

	@Override
	public void createMultiple(ArrayList<TermePara> obj, int lot) {
		
		StringBuilder r = new StringBuilder();
		
		Iterator<TermePara> it = obj.iterator();
		
		while(it.hasNext()){
			r.append("INSERT INTO TermePara (idTerme, idPara, poids, position, score) VALUES ");
			TermePara t = it.next();
			r.append("(").append(t.getIdTerme()).append(",").append(t.getIdPara()).append(",").append(t.getPoids()).append(",'").append(t.getPosition()).append("',").append(t.getScore()).append(")");
			for(int i=1; i<lot && it.hasNext(); i++){
				t = it.next();
				r.append(",");
				r.append("(").append(t.getIdTerme()).append(",").append(t.getIdPara()).append(",").append(t.getPoids()).append(",'").append(t.getPosition()).append("',").append(t.getScore()).append(")");
			}			
		}	
		
		try {
			this.connect.createStatement().executeUpdate(r.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		}		
	}
	
	@Override
	public void delete(TermePara obj) {
		try {			
            this.connect
               	.createStatement()
               	.executeUpdate(
               			"DELETE FROM TermePara " +
               			"WHERE idTerme = " + obj.getIdTerme() +
               			" AND idPara = " + obj.getIdPara()
                );		
	    } catch (SQLException e) {
	            e.printStackTrace();
	    }
	}
	
	@Override
	public TermePara find(int id) {
		return null;
	}
	
	public TermePara find(int idTerme, int idPara) {
		TermePara termePara = new TermePara();                
        
        try {
	        ResultSet result = this.connect
	                               .createStatement()
	                               .executeQuery(
	                                    "SELECT * FROM TermePara "+
	                                    " WHERE idTerme = "+ idTerme +
	                                    " AND idPara = "+ idPara
	                               );

            if(result.first()){
            	termePara = new TermePara(
            					result.getInt("idTerme"),
            					result.getInt("idPara"),
            					result.getInt("poids"),
            					result.getString("position"),
            					result.getFloat("score")
            				);            	
            }
            else{ termePara = null; }
        } catch (SQLException e) {
                e.printStackTrace();
        }       
        
        return termePara;
	}

	public Terme findTerme(int idT, int idP) {
		Terme terme = new Terme();
		
		try {
	        ResultSet result = this.connect
	                               .createStatement()
	                               .executeQuery(
	                                    "SELECT Terme.* " +
	                                    "FROM Terme, TermePara "+
	                                    " WHERE Terme.idTerme = " + idT +
	                                    " AND TermePara.idTerme = " + idT +
	                                    " AND TermePara.idPara = " + idP
	                               );

            if(result.first()){
            	terme.setIdTerme(result.getInt("idTerme"));
            	terme.setValueTerme(result.getString("terme"));         	
            }
            else{ return null; }
        } catch (SQLException e) {
                e.printStackTrace();
        }       
        
		return terme;
	}
	
	public Paragraphe findPara(int idT, int idP) {
		Paragraphe para = new Paragraphe();
		
		try {
	        ResultSet result = this.connect
	                               .createStatement()
	                               .executeQuery(
	                                    "SELECT * FROM Paragraphe, TermePara "+
	                                    " WHERE Paragraphe.idParagraphe = " + idP +
	                                    " AND TermePara.idPara = " + idP +
	                                    " AND TermePara.idTerme = " + idT
	                               );

            if(result.first()){
            	para.setIdParagraphe(result.getInt("idParagraphe"));
            	para.setxPath(result.getString("xPath"));    
            	para.setIdDocument(result.getInt("idDocument"));
            }
            else{ return null; }
        } catch (SQLException e) {
                e.printStackTrace();
        }       
        
		return para;
	}
	
	public ArrayList<TermePara> findParas(int idT) {
		ArrayList<TermePara> termesParas = new ArrayList<TermePara>();
		TermePara termePara = new TermePara();
		
		try {
	        ResultSet result = this.connect
	                               .createStatement()
	                               .executeQuery(
	                                    "SELECT TP.* FROM Terme, TermePara TP"+
	                                    " WHERE Terme.idTerme = " + idT +	                                    
	                                    " AND TP.idTerme = " + idT
	                               ); 

            if(result.first()){
            	result.beforeFirst();
        	
            	while(result.next()) {
	            	termePara.setIdTerme(idT);
	            	termePara.setIdPara(result.getInt("idPara"));
	            	termePara.setPoids(result.getInt("poids"));    
	            	termePara.setPosition(result.getString("position"));
	            	termePara.setScore(result.getFloat("score"));	            	
	            	          	
	            	termesParas.add(termePara);
	            	termePara = new TermePara();
            	}            	
            }
            else{ return null; }
        } catch (SQLException e) {
                e.printStackTrace();
        }            
		return termesParas;
	}
	
	@Override
	public TermePara update(TermePara obj) {
		try {			
            this .connect	
                 .createStatement()
                 .executeUpdate(
                	"UPDATE TermePara " +
                	"SET poids = " + obj.getPoids() + ","+
                	" position = " + obj.getPosition() +
                	" WHERE idTerme = " + obj.getIdTerme() +
                	" AND idPara = " + obj.getIdPara()
                 );
		
            obj = this.find(obj.getIdTerme(), obj.getIdPara());
	    } catch (SQLException e) {
	            e.printStackTrace();
	    }	    
	    return obj;
	}
	
	// TODO REMOVE si non utilisé
	public void updateScore(Float score, int idPara, int idTerme) {
		try {			
            this .connect	
                 .createStatement()
                 .executeUpdate(
                	"UPDATE TermePara " +
                	"SET score = " + score +
                	" WHERE idTerme = " + idTerme +
                	" AND idPara = " + idPara
                 );
	    } catch (SQLException e) {
	            e.printStackTrace();
	    }
	}

	// TODO REMOVE si non utilisé
	public int nbTermesInPara(int idP) {
		
		int nbTermsInPara = 0;
		try {
	        ResultSet result = this.connect
	                               .createStatement()
	                               .executeQuery(
	                            		   "SELECT COUNT(tp.idTerme) from TermePara tp where idPara = " + idP
	                               );

	        if(result.first()){
            	nbTermsInPara = result.getInt("COUNT(tp.idTerme)");
            }
        } catch (SQLException e) {
                e.printStackTrace();
        }
		return nbTermsInPara;
	}
	
	// TODO REMOVE si non utilisé
	public int nbParagrafWithTerm(int idT) {
		
		int nbParas = 0;
		try {
	        ResultSet result = this.connect
	                               .createStatement()
	                               .executeQuery(
	                            		   "SELECT COUNT(tp.idPara) from TermePara tp where idTerme = " + idT
	                               );

	        if(result.first()){
            	nbParas = result.getInt("COUNT(tp.idPara)");
            }
        } catch (SQLException e) {
                e.printStackTrace();
        }
		return nbParas;
	}

}
